#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库初始化脚本验证工具
验证初始化脚本是否能正确创建与现有数据库相同的结构
"""

import sys
import os
import sqlite3
import tempfile
import shutil
from datetime import datetime

sys.path.append('/home/gd/dispatch-system/backend')

def backup_current_db():
    """备份当前数据库"""
    current_db = '/home/gd/dispatch-system/backend/instance/dispatch_system.db'
    backup_name = f'dispatch_system_validation_backup_{datetime.now().strftime("%Y%m%d_%H%M%S")}.db'
    backup_path = f'/home/gd/dispatch-system/backend/instance/{backup_name}'
    
    if os.path.exists(current_db):
        shutil.copy2(current_db, backup_path)
        print(f"✅ 数据库已备份到: {backup_path}")
        return backup_path
    else:
        print(f"❌ 当前数据库不存在: {current_db}")
        return None

def get_table_schema(db_path):
    """获取数据库表结构"""
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # 获取所有表名
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
    tables = [row[0] for row in cursor.fetchall()]
    
    schema_info = {}
    
    for table in tables:
        # 获取表结构
        cursor.execute(f"PRAGMA table_info({table})")
        columns = cursor.fetchall()
        
        # 获取外键信息
        cursor.execute(f"PRAGMA foreign_key_list({table})")
        foreign_keys = cursor.fetchall()
        
        # 获取索引信息
        cursor.execute(f"PRAGMA index_list({table})")
        indexes = cursor.fetchall()
        
        schema_info[table] = {
            'columns': columns,
            'foreign_keys': foreign_keys,
            'indexes': indexes
        }
    
    conn.close()
    return schema_info

def create_test_database():
    """使用初始化脚本创建测试数据库"""
    # 创建临时数据库文件
    temp_db = tempfile.mktemp(suffix='.db')
    
    # 设置环境变量指向临时数据库
    original_db_url = os.environ.get('DATABASE_URL')
    os.environ['DATABASE_URL'] = f'sqlite:///{temp_db}'
    
    try:
        # 导入并创建应用
        from wsgi import create_app
        from app.models import db
        
        app = create_app()
        
        with app.app_context():
            # 创建所有表
            db.create_all()
            print(f"✅ 测试数据库创建成功: {temp_db}")
            
            # 运行初始化数据
            from app.models.user import User
            from app.models.group import Group
            from app.models.vehicle import Vehicle
            from app.models.material import Material
            from app.models.area import Area
            
            # 检查是否需要创建初始数据
            if not User.query.filter_by(username='admin').first():
                print("创建初始化数据...")
                from datetime import date
                
                # 创建默认管理员
                admin = User(
                    username='admin',
                    email='admin@example.com',
                    role='admin',
                    name='系统管理员',
                    employee_id='ADMIN001',
                    phone='00000000000',
                    position='系统管理员',
                    department='IT部',
                    id_card='000000000000000000',
                    ethnicity='汉族',
                    gender='男',
                    hire_date=date.today(),
                    is_driver=False
                )
                admin.set_password('admin123')
                db.session.add(admin)
                
                # 创建默认组
                if not Group.query.first():
                    group1 = Group(name='维修一组', description='负责A区域的维修任务')
                    group2 = Group(name='维修二组', description='负责B区域的维修任务')
                    db.session.add_all([group1, group2])
                
                # 创建默认区域
                if not Area.query.first():
                    area1 = Area(name='A区域', description='A区域维修范围')
                    area2 = Area(name='B区域', description='B区域维修范围')
                    area3 = Area(name='C区域', description='C区域维修范围')
                    db.session.add_all([area1, area2, area3])
                
                db.session.commit()
                print("✅ 初始化数据创建完成")
        
        return temp_db
        
    except Exception as e:
        print(f"❌ 创建测试数据库失败: {e}")
        if os.path.exists(temp_db):
            os.remove(temp_db)
        return None
    finally:
        # 恢复原始数据库URL
        if original_db_url:
            os.environ['DATABASE_URL'] = original_db_url
        elif 'DATABASE_URL' in os.environ:
            del os.environ['DATABASE_URL']

def compare_schemas(original_schema, test_schema):
    """比较两个数据库的结构"""
    print("\n" + "=" * 60)
    print("数据库结构比较")
    print("=" * 60)
    
    differences = []
    
    # 比较表
    original_tables = set(original_schema.keys())
    test_tables = set(test_schema.keys())
    
    missing_tables = original_tables - test_tables
    extra_tables = test_tables - original_tables
    
    if missing_tables:
        print(f"❌ 测试数据库缺失表: {missing_tables}")
        differences.append(f"缺失表: {missing_tables}")
    
    if extra_tables:
        print(f"⚠️  测试数据库多余表: {extra_tables}")
        differences.append(f"多余表: {extra_tables}")
    
    # 比较共同表的结构
    common_tables = original_tables & test_tables
    
    for table in sorted(common_tables):
        print(f"\n检查表: {table}")
        
        orig_cols = original_schema[table]['columns']
        test_cols = test_schema[table]['columns']
        
        # 比较列数量
        if len(orig_cols) != len(test_cols):
            print(f"  ❌ 列数量不匹配: 原始={len(orig_cols)}, 测试={len(test_cols)}")
            differences.append(f"{table}: 列数量不匹配")
        
        # 比较列结构
        orig_col_dict = {col[1]: col for col in orig_cols}  # col[1] is column name
        test_col_dict = {col[1]: col for col in test_cols}
        
        orig_col_names = set(orig_col_dict.keys())
        test_col_names = set(test_col_dict.keys())
        
        missing_cols = orig_col_names - test_col_names
        extra_cols = test_col_names - orig_col_names
        
        if missing_cols:
            print(f"  ❌ 缺失列: {missing_cols}")
            differences.append(f"{table}: 缺失列 {missing_cols}")
        
        if extra_cols:
            print(f"  ⚠️  多余列: {extra_cols}")
            differences.append(f"{table}: 多余列 {extra_cols}")
        
        # 比较共同列的属性
        common_cols = orig_col_names & test_col_names
        for col_name in common_cols:
            orig_col = orig_col_dict[col_name]
            test_col = test_col_dict[col_name]
            
            # 比较列属性 (cid, name, type, notnull, dflt_value, pk)
            if orig_col[2] != test_col[2]:  # type
                print(f"  ⚠️  列 {col_name} 类型不匹配: 原始={orig_col[2]}, 测试={test_col[2]}")
            
            if orig_col[3] != test_col[3]:  # notnull
                print(f"  ⚠️  列 {col_name} NOT NULL约束不匹配: 原始={orig_col[3]}, 测试={test_col[3]}")
            
            if orig_col[5] != test_col[5]:  # primary key
                print(f"  ⚠️  列 {col_name} 主键约束不匹配: 原始={orig_col[5]}, 测试={test_col[5]}")
        
        # 比较外键
        orig_fks = original_schema[table]['foreign_keys']
        test_fks = test_schema[table]['foreign_keys']
        
        if len(orig_fks) != len(test_fks):
            print(f"  ⚠️  外键数量不匹配: 原始={len(orig_fks)}, 测试={len(test_fks)}")
            differences.append(f"{table}: 外键数量不匹配")
        
        if not missing_cols and not extra_cols and len(orig_cols) == len(test_cols):
            print(f"  ✅ 表 {table} 结构一致")
    
    return differences

def main():
    """主函数"""
    print("数据库初始化脚本验证工具")
    print("=" * 60)
    
    # 1. 备份当前数据库
    print("\n1. 备份当前数据库")
    backup_path = backup_current_db()
    if not backup_path:
        print("❌ 无法备份数据库，退出验证")
        return
    
    # 2. 获取当前数据库结构
    print("\n2. 分析当前数据库结构")
    current_db = '/home/gd/dispatch-system/backend/instance/dispatch_system.db'
    original_schema = get_table_schema(current_db)
    print(f"✅ 当前数据库包含 {len(original_schema)} 个表")
    
    # 3. 使用初始化脚本创建测试数据库
    print("\n3. 使用初始化脚本创建测试数据库")
    test_db = create_test_database()
    if not test_db:
        print("❌ 无法创建测试数据库，退出验证")
        return
    
    # 4. 获取测试数据库结构
    print("\n4. 分析测试数据库结构")
    test_schema = get_table_schema(test_db)
    print(f"✅ 测试数据库包含 {len(test_schema)} 个表")
    
    # 5. 比较结构
    print("\n5. 比较数据库结构")
    differences = compare_schemas(original_schema, test_schema)
    
    # 6. 清理测试数据库
    if os.path.exists(test_db):
        os.remove(test_db)
        print(f"\n✅ 测试数据库已清理: {test_db}")
    
    # 7. 总结
    print("\n" + "=" * 60)
    print("验证总结")
    print("=" * 60)
    
    if not differences:
        print("✅ 初始化脚本能够正确创建与现有数据库相同的结构")
        print("✅ 数据库初始化脚本验证通过")
    else:
        print("⚠️  发现以下差异:")
        for diff in differences:
            print(f"  - {diff}")
        print("⚠️  建议检查初始化脚本或迁移脚本")
    
    print(f"\n备份文件位置: {backup_path}")
    print("验证完成!")

if __name__ == '__main__':
    try:
        main()
    except Exception as e:
        print(f"验证过程中出现错误: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)